<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>MySQL事件 | lilei</title>
    <meta name="generator" content="VuePress 1.8.2">
    <link rel="icon" href="/images/icons/logo.png">
    <link rel="apple-touch-icon" href="/images/icons/logo.png">
    <link rel="manifest" href="/manifest.json">
    <script type="text/javascript" src="/assets/js/load.js"></script>
    <meta name="description" content="个人网站">
    <meta name="twitter:title" content="MySQL事件">
    <meta name="twitter:description" content="">
    <meta name="twitter:card" content="summary">
    <meta name="twitter:url" content="http://www.lileiblog.xyz/SQL/MySQL%E4%BA%8B%E4%BB%B6.html">
    <meta property="og:type" content="article">
    <meta property="og:title" content="MySQL事件">
    <meta property="og:description" content="">
    <meta property="og:url" content="http://www.lileiblog.xyz/SQL/MySQL%E4%BA%8B%E4%BB%B6.html">
    <meta property="og:site_name" content="lilei">
    <meta itemprop="name" content="MySQL事件">
    <meta itemprop="description" content="">
    <meta name="theme-color" content="#3eaf7c">
    <meta name="apple-mobile-web-app-capable" content="yes">
    <meta name="apple-mobile-web-app-status-bar-style" content="black">
    <meta name="msapplication-TileImage" content="/images/icons/logo.png">
    <meta name="msapplication-TileColor" content="#ffffff">
    <meta name="keywords" content="lilei,vue,vuepress,vuepress-theme,theme,主题,vuepress主题,blog
        ,vuepress-blog,java,python,shell,sql,golang,script,shell script,nginx,windows,去广告,androd,google drive
        ,one drive,idea,eclipse,git,小程序">
    <meta name="referrer" content="never">
    
    <link rel="preload" href="/assets/css/0.styles.8932179b.css" as="style"><link rel="preload" href="/assets/js/app.269009d6.js" as="script"><link rel="preload" href="/assets/js/3.974b9720.js" as="script"><link rel="preload" href="/assets/js/48.df9d5d3b.js" as="script"><link rel="prefetch" href="/assets/js/10.5915e99b.js"><link rel="prefetch" href="/assets/js/11.ff732762.js"><link rel="prefetch" href="/assets/js/12.8bb86d91.js"><link rel="prefetch" href="/assets/js/13.18bde5b0.js"><link rel="prefetch" href="/assets/js/14.314fae34.js"><link rel="prefetch" href="/assets/js/15.0a4ff48f.js"><link rel="prefetch" href="/assets/js/16.ffaf1503.js"><link rel="prefetch" href="/assets/js/17.49a31a6e.js"><link rel="prefetch" href="/assets/js/18.7e6443bf.js"><link rel="prefetch" href="/assets/js/19.6880cc9a.js"><link rel="prefetch" href="/assets/js/20.1fb3f255.js"><link rel="prefetch" href="/assets/js/21.0cd39eea.js"><link rel="prefetch" href="/assets/js/22.392d85cb.js"><link rel="prefetch" href="/assets/js/23.f50adb15.js"><link rel="prefetch" href="/assets/js/24.43e324e1.js"><link rel="prefetch" href="/assets/js/25.e3663205.js"><link rel="prefetch" href="/assets/js/26.da141fb8.js"><link rel="prefetch" href="/assets/js/27.6c05235a.js"><link rel="prefetch" href="/assets/js/28.c124245a.js"><link rel="prefetch" href="/assets/js/29.d185870d.js"><link rel="prefetch" href="/assets/js/30.8587d422.js"><link rel="prefetch" href="/assets/js/31.dc382b56.js"><link rel="prefetch" href="/assets/js/32.20ba32e9.js"><link rel="prefetch" href="/assets/js/33.6ecd7de9.js"><link rel="prefetch" href="/assets/js/34.2b91c13a.js"><link rel="prefetch" href="/assets/js/35.66ed0733.js"><link rel="prefetch" href="/assets/js/36.ce7bca84.js"><link rel="prefetch" href="/assets/js/37.d00bb5ac.js"><link rel="prefetch" href="/assets/js/38.c3ecbb6f.js"><link rel="prefetch" href="/assets/js/39.308f4175.js"><link rel="prefetch" href="/assets/js/4.520f6ecc.js"><link rel="prefetch" href="/assets/js/40.f4b98bc2.js"><link rel="prefetch" href="/assets/js/41.95fae64f.js"><link rel="prefetch" href="/assets/js/42.fe4f877b.js"><link rel="prefetch" href="/assets/js/43.ca8f222e.js"><link rel="prefetch" href="/assets/js/44.4d4ee79e.js"><link rel="prefetch" href="/assets/js/45.79178b33.js"><link rel="prefetch" href="/assets/js/46.c3817d0b.js"><link rel="prefetch" href="/assets/js/47.b8252443.js"><link rel="prefetch" href="/assets/js/49.d812fdb2.js"><link rel="prefetch" href="/assets/js/5.f1a24a16.js"><link rel="prefetch" href="/assets/js/50.55cfcaa8.js"><link rel="prefetch" href="/assets/js/51.b8608b02.js"><link rel="prefetch" href="/assets/js/52.52c5e00d.js"><link rel="prefetch" href="/assets/js/53.3a9bd5fb.js"><link rel="prefetch" href="/assets/js/54.a4240c13.js"><link rel="prefetch" href="/assets/js/55.01c4b876.js"><link rel="prefetch" href="/assets/js/56.f29549a3.js"><link rel="prefetch" href="/assets/js/57.01438a04.js"><link rel="prefetch" href="/assets/js/58.29bb4626.js"><link rel="prefetch" href="/assets/js/59.e8b89408.js"><link rel="prefetch" href="/assets/js/6.8c478b39.js"><link rel="prefetch" href="/assets/js/60.bedb5381.js"><link rel="prefetch" href="/assets/js/61.bdfbe584.js"><link rel="prefetch" href="/assets/js/62.cb825d1a.js"><link rel="prefetch" href="/assets/js/63.a9516572.js"><link rel="prefetch" href="/assets/js/64.aa854ed7.js"><link rel="prefetch" href="/assets/js/65.285f883d.js"><link rel="prefetch" href="/assets/js/66.12849f37.js"><link rel="prefetch" href="/assets/js/67.c18b00e4.js"><link rel="prefetch" href="/assets/js/68.fa59daa9.js"><link rel="prefetch" href="/assets/js/69.e9d5dda4.js"><link rel="prefetch" href="/assets/js/7.cfb0d0ea.js"><link rel="prefetch" href="/assets/js/70.db126cce.js"><link rel="prefetch" href="/assets/js/71.6acb04cd.js"><link rel="prefetch" href="/assets/js/72.7ff0ab11.js"><link rel="prefetch" href="/assets/js/73.9a66da79.js"><link rel="prefetch" href="/assets/js/74.0a38a030.js"><link rel="prefetch" href="/assets/js/75.7b32dbb3.js"><link rel="prefetch" href="/assets/js/8.cb9c08e5.js"><link rel="prefetch" href="/assets/js/9.ad652cfe.js"><link rel="prefetch" href="/assets/js/vendors~flowchart.110a1065.js">
    <link rel="stylesheet" href="/assets/css/0.styles.8932179b.css">
  </head>
  <body>
    <div id="app" data-server-rendered="true"><div class="theme-container"><header class="navbar"><div class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/" class="home-link router-link-active"><img src="/images/icons/logo.png" alt="lilei" class="logo"> <span class="site-name can-hide">lilei</span></a> <div class="links"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/Form/" class="nav-link">
  Form
</a></div><div class="nav-item"><a href="/IDE/" class="nav-link">
  IDE
</a></div><div class="nav-item"><a href="/Java/" class="nav-link">
  Java
</a></div><div class="nav-item"><a href="/Other/" class="nav-link">
  Other
</a></div><div class="nav-item"><a href="/Python/" class="nav-link">
  Python
</a></div><div class="nav-item"><a href="/SQL/" class="nav-link router-link-active">
  SQL
</a></div><div class="nav-item"><a href="/Shell/" class="nav-link">
  Shell
</a></div><div class="nav-item"><a href="/System/" class="nav-link">
  System
</a></div> <a href="https://github.com/woytu/notes-vuepress" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav></div></header> <div class="sidebar-mask"></div> <aside class="sidebar"><nav class="nav-links"><div class="nav-item"><a href="/Form/" class="nav-link">
  Form
</a></div><div class="nav-item"><a href="/IDE/" class="nav-link">
  IDE
</a></div><div class="nav-item"><a href="/Java/" class="nav-link">
  Java
</a></div><div class="nav-item"><a href="/Other/" class="nav-link">
  Other
</a></div><div class="nav-item"><a href="/Python/" class="nav-link">
  Python
</a></div><div class="nav-item"><a href="/SQL/" class="nav-link router-link-active">
  SQL
</a></div><div class="nav-item"><a href="/Shell/" class="nav-link">
  Shell
</a></div><div class="nav-item"><a href="/System/" class="nav-link">
  System
</a></div> <a href="https://github.com/woytu/notes-vuepress" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav>  <ul class="sidebar-links"><li><section class="sidebar-group depth-0"><p class="sidebar-heading open"><span>SQL</span> <!----></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/SQL/" aria-current="page" class="sidebar-link">SQL</a></li><li><a href="/SQL/MySQL事件.html" class="active sidebar-link">MySQL事件</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/SQL/MySQL事件.html#事件是什么" class="sidebar-link">事件是什么</a></li><li class="sidebar-sub-header"><a href="/SQL/MySQL事件.html#查看事件调度器" class="sidebar-link">查看事件调度器</a></li><li class="sidebar-sub-header"><a href="/SQL/MySQL事件.html#开启事件调度器" class="sidebar-link">开启事件调度器</a></li><li class="sidebar-sub-header"><a href="/SQL/MySQL事件.html#查看事件" class="sidebar-link">查看事件</a></li><li class="sidebar-sub-header"><a href="/SQL/MySQL事件.html#查看事件创建语句" class="sidebar-link">查看事件创建语句</a></li><li class="sidebar-sub-header"><a href="/SQL/MySQL事件.html#创建事件" class="sidebar-link">创建事件</a></li><li class="sidebar-sub-header"><a href="/SQL/MySQL事件.html#修改事件" class="sidebar-link">修改事件</a></li><li class="sidebar-sub-header"><a href="/SQL/MySQL事件.html#删除事件" class="sidebar-link">删除事件</a></li><li class="sidebar-sub-header"><a href="/SQL/MySQL事件.html#开启事件" class="sidebar-link">开启事件</a></li><li class="sidebar-sub-header"><a href="/SQL/MySQL事件.html#停止事件" class="sidebar-link">停止事件</a></li><li class="sidebar-sub-header"><a href="/SQL/MySQL事件.html#启动定时器" class="sidebar-link">启动定时器</a></li></ul></li><li><a href="/SQL/MySQL备份恢复数据.html" class="sidebar-link">MySQL备份恢复数据</a></li><li><a href="/SQL/MySQL存储过程.html" class="sidebar-link">MySQL存储过程</a></li><li><a href="/SQL/MySQL安装配置.html" class="sidebar-link">MySQL安装配置</a></li><li><a href="/SQL/MySQL常见问题.html" class="sidebar-link">MySQL常见问题</a></li><li><a href="/SQL/MySQL笔记.html" class="sidebar-link">MySQL笔记</a></li><li><a href="/SQL/mysql-udf安装.html" class="sidebar-link">mysql-udf安装</a></li></ul></section></li></ul> </aside> <main class="page"> <div class="theme-default-content content__default"><h1 id="mysql事件"><a href="#mysql事件" class="header-anchor">#</a> MySQL事件</h1> <p></p><div class="table-of-contents"><ul><li><a href="#事件是什么">事件是什么</a></li><li><a href="#查看事件调度器">查看事件调度器</a></li><li><a href="#开启事件调度器">开启事件调度器</a></li><li><a href="#查看事件">查看事件</a></li><li><a href="#查看事件创建语句">查看事件创建语句</a></li><li><a href="#创建事件">创建事件</a><ul><li><a href="#每隔一秒执行">每隔一秒执行</a></li><li><a href="#每天凌晨1点执行">每天凌晨1点执行</a></li><li><a href="#每隔一分钟执行">每隔一分钟执行</a></li><li><a href="#指定时间范围内执行">指定时间范围内执行</a></li><li><a href="#一小时后只执行一次">一小时后只执行一次</a></li></ul></li><li><a href="#修改事件">修改事件</a></li><li><a href="#删除事件">删除事件</a></li><li><a href="#开启事件">开启事件</a></li><li><a href="#停止事件">停止事件</a></li><li><a href="#启动定时器">启动定时器</a></li></ul></div><p></p> <h2 id="事件是什么"><a href="#事件是什么" class="header-anchor">#</a> 事件是什么</h2> <blockquote><p>MySQL5.1版本开始引进event概念，MySQL中的事件(event：时间触发器)是用于执行定时或周期性的任务，类似Linux中的<code>crontab</code>，
但是后者只能精确到分钟，事件可以精确到秒。</p> <p>通过单独或调用存储过程使用，在某一特定的时间点，触发相关的SQL语句或存储过程。</p> <p>事件由一个特定的线程来管理的，也就是所谓的事件调度器，但是事件不能直接调用。</p></blockquote> <blockquote><p>整个服务器重启、断电会导致event事件恢复成默认<code>OFF</code>关闭状态，需要在<code>mysql.ini</code>文件中修改加入<code>event_scheduler = ON</code></p></blockquote> <h2 id="查看事件调度器"><a href="#查看事件调度器" class="header-anchor">#</a> 查看事件调度器</h2> <blockquote><p>MySQL中调度器<code>event_scheduler</code>负责调用事件，也就是由全局变量<code>event_scheduler</code>的状态决定，它默认是<code>OFF</code>, 一般是<code>OFF</code>。</p></blockquote> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">SHOW</span> VARIABLES <span class="token operator">LIKE</span> <span class="token string">'%event_scheduler%'</span><span class="token punctuation">;</span>
</code></pre></div><h2 id="开启事件调度器"><a href="#开启事件调度器" class="header-anchor">#</a> 开启事件调度器</h2> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> event_scheduler<span class="token operator">=</span><span class="token number">1</span>
</code></pre></div><h2 id="查看事件"><a href="#查看事件" class="header-anchor">#</a> 查看事件</h2> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">SHOW</span> EVENTS<span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> information_schema<span class="token punctuation">.</span>events<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> mysql<span class="token punctuation">.</span>event<span class="token punctuation">;</span>
</code></pre></div><h2 id="查看事件创建语句"><a href="#查看事件创建语句" class="header-anchor">#</a> 查看事件创建语句</h2> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">SHOW</span> <span class="token keyword">CREATE</span> EVENT event_name
</code></pre></div><h2 id="创建事件"><a href="#创建事件" class="header-anchor">#</a> 创建事件</h2> <blockquote><p><code>EVERY</code>后面可以跟可选的<code>STARTS</code>和<code>ENDS</code>，指定事件开始和结束时间，在这个时间段内，时间定时执行。
<code>STARTS</code>和<code>ENDS</code>可同时指定，或者只指定<code>STARTS</code>，或两者都不指定。</p></blockquote> <h3 id="每隔一秒执行"><a href="#每隔一秒执行" class="header-anchor">#</a> 每隔一秒执行</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">DELIMITER</span> $$

<span class="token keyword">CREATE</span> EVENT <span class="token punctuation">`</span>executed_every_second<span class="token punctuation">`</span>

<span class="token comment">-- 设置触发点</span>
<span class="token keyword">ON</span> SCHEDULE
	<span class="token comment">-- 使用EVERY关键字指定时间间隔，每隔一秒执行</span>
	EVERY <span class="token number">1</span> <span class="token keyword">SECOND</span>

<span class="token comment">-- 可以设置这个事件是执行一次还是持久执行，默认为`NOT PRESERVE`</span>
<span class="token comment">-- 即默认的计划任务执行完毕后自动drop该事件</span>
<span class="token keyword">ON</span> COMPLETION PRESERVE 
<span class="token comment">-- 可是设置该事件创建后状态是否开启或关闭，默认为`ENABLE`</span>
<span class="token keyword">ENABLE</span>
<span class="token comment">-- 可以给该事件加上注释，最大长度64个字节</span>
<span class="token keyword">COMMENT</span> <span class="token string">&quot;每隔一秒执行&quot;</span>
<span class="token keyword">DO</span>
	<span class="token keyword">BEGIN</span>
		
		<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token keyword">USER</span> <span class="token keyword">LIMIT</span> <span class="token number">10</span><span class="token punctuation">;</span>
		
	<span class="token keyword">END</span>$$

<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>
</code></pre></div><h3 id="每天凌晨1点执行"><a href="#每天凌晨1点执行" class="header-anchor">#</a> 每天凌晨1点执行</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">DELIMITER</span> $$

<span class="token keyword">CREATE</span> EVENT <span class="token punctuation">`</span><span class="token number">1</span>_am_every_day<span class="token punctuation">`</span>

<span class="token comment">-- 设置触发点</span>
<span class="token keyword">ON</span> SCHEDULE
	<span class="token comment">-- 使用EVERY关键字指定时间间隔，每天启动时间</span>
	EVERY <span class="token number">1</span> <span class="token keyword">DAY</span> STARTS DATE_ADD<span class="token punctuation">(</span>DATE_ADD<span class="token punctuation">(</span>CURDATE<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">DAY</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">HOUR</span><span class="token punctuation">)</span>

<span class="token comment">-- 可以设置这个事件是执行一次还是持久执行，默认为`NOT PRESERVE`</span>
<span class="token comment">-- 即默认的计划任务执行完毕后自动drop该事件</span>
<span class="token keyword">ON</span> COMPLETION PRESERVE 
<span class="token comment">-- 可是设置该事件创建后状态是否开启或关闭，默认为`ENABLE`</span>
<span class="token keyword">ENABLE</span>
<span class="token comment">-- 可以给该事件加上注释，最大长度64个字节</span>
<span class="token keyword">COMMENT</span> <span class="token string">&quot;每天凌晨1点执行&quot;</span>
<span class="token keyword">DO</span>
	<span class="token keyword">BEGIN</span>
		
		<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token keyword">USER</span> <span class="token keyword">LIMIT</span> <span class="token number">10</span><span class="token punctuation">;</span>
		
	<span class="token keyword">END</span>$$

<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>
</code></pre></div><h3 id="每隔一分钟执行"><a href="#每隔一分钟执行" class="header-anchor">#</a> 每隔一分钟执行</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">DELIMITER</span> $$

<span class="token keyword">CREATE</span> EVENT <span class="token punctuation">`</span>executed_every_minute<span class="token punctuation">`</span>

<span class="token comment">-- 设置触发点</span>
<span class="token keyword">ON</span> SCHEDULE
	<span class="token comment">-- 使用EVERY关键字指定时间间隔，每隔一分钟执行</span>
	EVERY <span class="token number">1</span> <span class="token keyword">MINUTE</span>

<span class="token comment">-- 可以设置这个事件是执行一次还是持久执行，默认为`NOT PRESERVE`</span>
<span class="token comment">-- 即默认的计划任务执行完毕后自动drop该事件</span>
<span class="token keyword">ON</span> COMPLETION PRESERVE 
<span class="token comment">-- 可是设置该事件创建后状态是否开启或关闭，默认为`ENABLE`</span>
<span class="token keyword">ENABLE</span>
<span class="token comment">-- 可以给该事件加上注释，最大长度64个字节</span>
<span class="token keyword">COMMENT</span> <span class="token string">&quot;每隔一分钟执行&quot;</span>
<span class="token keyword">DO</span>
	<span class="token keyword">BEGIN</span>
		
		<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token keyword">USER</span> <span class="token keyword">LIMIT</span> <span class="token number">10</span><span class="token punctuation">;</span>
		
	<span class="token keyword">END</span>$$

<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>
</code></pre></div><h3 id="指定时间范围内执行"><a href="#指定时间范围内执行" class="header-anchor">#</a> 指定时间范围内执行</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">DELIMITER</span> $$

<span class="token keyword">CREATE</span> EVENT <span class="token punctuation">`</span><span class="token number">1</span>_am_every_range<span class="token punctuation">`</span>

<span class="token comment">-- 设置触发点</span>
<span class="token keyword">ON</span> SCHEDULE
	<span class="token comment">-- 使用EVERY关键字指定时间间隔，每隔一分钟执行</span>
	EVERY <span class="token number">1</span> <span class="token keyword">MINUTE</span>
	<span class="token comment">-- 使用STARTS关键字指定开始时间，当前时间一小时之后开始执行</span>
	STARTS <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token operator">+</span> <span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">HOUR</span>
	<span class="token comment">-- 使用ENDS关键字指定结束时间，当前时间一个月后结束执行</span>
	ENDS <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token operator">+</span> <span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">MONTH</span>

<span class="token comment">-- 可以设置这个事件是执行一次还是持久执行，默认为`NOT PRESERVE`</span>
<span class="token comment">-- 即默认的计划任务执行完毕后自动drop该事件</span>
<span class="token keyword">ON</span> COMPLETION PRESERVE 
<span class="token comment">-- 可是设置该事件创建后状态是否开启或关闭，默认为`ENABLE`</span>
<span class="token keyword">ENABLE</span>
<span class="token comment">-- 可以给该事件加上注释，最大长度64个字节</span>
<span class="token keyword">COMMENT</span> <span class="token string">&quot;每隔一分钟执行，指定开始时间和结束时间&quot;</span>
<span class="token keyword">DO</span>
	<span class="token keyword">BEGIN</span>
		
		<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token keyword">USER</span> <span class="token keyword">LIMIT</span> <span class="token number">10</span><span class="token punctuation">;</span>
		
	<span class="token keyword">END</span>$$

<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>
</code></pre></div><h3 id="一小时后只执行一次"><a href="#一小时后只执行一次" class="header-anchor">#</a> 一小时后只执行一次</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">DELIMITER</span> $$

<span class="token keyword">CREATE</span> EVENT <span class="token punctuation">`</span>after_hour_execute_once<span class="token punctuation">`</span>

<span class="token comment">-- 设置触发点</span>
<span class="token keyword">ON</span> SCHEDULE
	<span class="token comment">-- 使用AT关键字指定只执行一次的时间，一小时后只执行一次</span>
	AT <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token operator">+</span> <span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">HOUR</span>

<span class="token comment">-- 可以设置这个事件是执行一次还是持久执行，默认为`NOT PRESERVE`</span>
<span class="token comment">-- 即默认的计划任务执行完毕后自动drop该事件</span>
<span class="token keyword">ON</span> COMPLETION PRESERVE 
<span class="token comment">-- 可是设置该事件创建后状态是否开启或关闭，默认为`ENABLE`</span>
<span class="token keyword">ENABLE</span>
<span class="token comment">-- 可以给该事件加上注释，最大长度64个字节</span>
<span class="token keyword">COMMENT</span> <span class="token string">&quot;一小时后只执行一次&quot;</span>
<span class="token keyword">DO</span>
	<span class="token keyword">BEGIN</span>
		
		<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token keyword">USER</span> <span class="token keyword">LIMIT</span> <span class="token number">10</span><span class="token punctuation">;</span>
		
	<span class="token keyword">END</span>$$

<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>
</code></pre></div><h2 id="修改事件"><a href="#修改事件" class="header-anchor">#</a> 修改事件</h2> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">ALTER</span> EVENT event_name
  <span class="token punctuation">[</span><span class="token keyword">ON</span> SCHEDULE SCHEDULE<span class="token punctuation">]</span>
  <span class="token punctuation">[</span><span class="token keyword">RENAME</span> <span class="token keyword">TO</span> new_event_name<span class="token punctuation">]</span>
  <span class="token punctuation">[</span><span class="token keyword">ON</span> COMPLETION <span class="token punctuation">[</span><span class="token operator">NOT</span><span class="token punctuation">]</span> PRESERVE<span class="token punctuation">]</span>
  <span class="token punctuation">[</span><span class="token keyword">COMMENT</span> <span class="token string">'comment'</span><span class="token punctuation">]</span>
  <span class="token punctuation">[</span><span class="token keyword">ENABLE</span> <span class="token operator">|</span> <span class="token keyword">DISABLE</span><span class="token punctuation">]</span>
  <span class="token punctuation">[</span><span class="token keyword">DO</span> sql_statement<span class="token punctuation">]</span>
</code></pre></div><h2 id="删除事件"><a href="#删除事件" class="header-anchor">#</a> 删除事件</h2> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">DROP</span> EVENT <span class="token punctuation">[</span><span class="token keyword">IF</span> <span class="token keyword">EXISTS</span><span class="token punctuation">]</span> event_name
</code></pre></div><h2 id="开启事件"><a href="#开启事件" class="header-anchor">#</a> 开启事件</h2> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">ALTER</span> EVENT event_name <span class="token keyword">ENABLE</span><span class="token punctuation">;</span>
</code></pre></div><h2 id="停止事件"><a href="#停止事件" class="header-anchor">#</a> 停止事件</h2> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">ALTER</span> EVENT event_name <span class="token keyword">DISABLE</span><span class="token punctuation">;</span>
</code></pre></div><h2 id="启动定时器"><a href="#启动定时器" class="header-anchor">#</a> 启动定时器</h2> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">ALTER</span> EVENT event_remind_status <span class="token keyword">ON</span>    
COMPLETION PRESERVE <span class="token keyword">ENABLE</span><span class="token punctuation">;</span> 
</code></pre></div></div> <footer class="page-edit"><div class="edit-link"><a href="https://github.com/woytu/notes-vuepress/edit/master//SQL/MySQL事件.md" target="_blank" rel="noopener noreferrer">在 GitHub 上编辑此页</a> <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></div> <div class="last-updated"><span class="prefix">上次更新:</span> <span class="time">2020-04-13 18:01:56</span></div></footer> <div class="page-nav"><p class="inner"><span class="prev">
      ←
      <a href="/SQL/" class="prev router-link-active">
        SQL
      </a></span> <span class="next"><a href="/SQL/MySQL备份恢复数据.html">
        MySQL备份恢复数据
      </a>
      →
    </span></p></div> </main></div><div class="global-ui"><!----></div></div>
    <script src="/assets/js/app.269009d6.js" defer></script><script src="/assets/js/3.974b9720.js" defer></script><script src="/assets/js/48.df9d5d3b.js" defer></script>
  </body>
</html>
